package cn.hchaojie.daily.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import cn.hchaojie.daily.dao.DailyDao;
import cn.hchaojie.daily.entity.Daily;
import cn.hchaojie.daily.entity.Keyword;

public class DailyDaoImpl implements DailyDao {
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/daily";
	private static final String NAME = "root";
	private static final String PASSWORD = "root";
	
	static {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	private static Connection createConnection() throws SQLException {
		return DriverManager.getConnection(URL, NAME, PASSWORD);
	}

	@Override
	public List<Daily> getDailies(int page, int size) {
		Connection connection = null;
		PreparedStatement psDaily = null;	// 日报查询
		ResultSet rsDaily = null;

		PreparedStatement psKeywords = null; // 日报关键字查询
		ResultSet rsKeywords = null;

		List<Daily> dailies = new ArrayList<>();

		// 加载驱动
		try {
			// 创建连接
			connection = createConnection();

			// 查询日报，关联查询用户名
			psDaily = connection
					.prepareStatement("select d.id, d.content, d.created, d.uid, d.digest, d.views, u.name user from daily d "
							+ "inner join user u on u.id = d.uid "
							+ "limit ?, ?");
			psDaily.setInt(1, (page - 1) * size);
			psDaily.setInt(2, size);
			rsDaily = psDaily.executeQuery();

			// 封装日报信息
			while (rsDaily.next()) {
				Daily daily = new Daily();
				daily.setId(rsDaily.getLong("id"));
				daily.setContent(rsDaily.getString("content"));
				daily.setCreated(rsDaily.getTimestamp("created"));
				daily.setUid(rsDaily.getLong("uid"));
				daily.setDigest(rsDaily.getString("digest"));
				daily.setViews(rsDaily.getInt("views"));
				daily.setUser( rsDaily.getString("user"));
				
				dailies.add(daily);
			}
			
			// 查询日报对应的关键字
			String query = buildDailyKeywordsQuery(dailies);
			psKeywords = connection.prepareStatement(query);
			rsKeywords = psKeywords.executeQuery();
			
			// 将关键字设置到对应的日报
			associateKeywordsAndDailies(dailies, rsKeywords);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rsDaily);
			close(psDaily);
			close(connection);
		}
		
		return dailies;
	}
	
	// 查询日报对应的关键字
	private static String buildDailyKeywordsQuery(List<Daily> dailies) {
		StringBuilder sb = new StringBuilder();
		
		sb.append("select k.id, k.name, dk.did from keyword k"
				+ " inner join daily_keyword dk on dk.kid = k.id"
				+ " inner join daily d on d.id = dk.did"
				+ " where d.id in (");
		
		for (Daily d : dailies) {
			sb.append(d.getId());
			sb.append(',');
		}
		
		sb.deleteCharAt(sb.length() - 1);
		
		sb.append(")");
		
		return sb.toString();
	}

	// 将日报和对应的关键字进行关联
	private static void associateKeywordsAndDailies(List<Daily> dailies, ResultSet keywords) throws SQLException {
		// 建立id和daily的映射关系，方便后面根据id查daily
		Map<Long, Daily> dailyMap = new HashMap<>();
		dailies.forEach(d -> dailyMap.put(d.getId(), d));
		
		while(keywords.next()) {
			Long id = keywords.getLong("id"); 			// id
			String name = keywords.getString("name");			// name
			Long did = keywords.getLong("did");			// dailyid
			
			if (dailyMap.containsKey(did)) {
				dailyMap.get(did).getKeyword().add(new Keyword(id, name));
			}
		}
	}

	private static void close(AutoCloseable c) {
		if (c != null) {
			try {
				c.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	@Override
	public void insert(Daily d) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		
		// 加载驱动
		try {
			// 创建连接
			connection = createConnection();

			// 发送sql请求、得到一个结果集
			String insert = "insert into daily"
					+ "(content, digest, uid, updated_by) "
					+ "values (?, ?, ?, ?)";
			statement = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);	// 第二个参数表示插入后，数据库需要返回主键
			statement.setString(1, d.getContent());
			statement.setString(2, d.getDigest());
			statement.setLong(3, d.getUid());
			statement.setLong(4, d.getUid());
			
			statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(resultSet);
			close(statement);
			close(connection);
		}
	}

	@Override
	public int count() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		
		// 加载驱动
		try {
			// 创建连接
			connection = createConnection();

			// 发送sql请求、得到一个结果集
			statement = connection.prepareStatement("select count(id) as total from daily");
			
			ResultSet rs = statement.executeQuery();
			rs.next();
			int total = rs.getInt("total");
			
			return total;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(resultSet);
			close(statement);
			close(connection);
		}
		
		return 0;
	}

	@Override
	public void insert(Daily d, String[] keywords) {
		Connection connection = null;
		PreparedStatement psDaily = null;		// 日报表相关查询
		ResultSet rsDaily = null;

		PreparedStatement psKeywords = null;	// 关键字查询相关sql
		ResultSet rsKeywords = null;
		
		PreparedStatement psNewKeywords = null;	// 关键字插入相关sql
		ResultSet rsNewKeywords = null;
		
		PreparedStatement psDailyKeywords = null;	// 日报-关键字关联表相关sql
		
		
		// 加载驱动
		try {
			// 创建连接
			connection = createConnection();

			// 发送sql请求、得到一个结果集
			String insert = "insert into daily"
					+ "(content, digest, uid, updated_by) "
					+ "values (?, ?, ?, ?)";
			psDaily = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
			psDaily.setString(1, d.getContent());
			psDaily.setString(2, d.getDigest());
			psDaily.setLong(3, d.getUid());
			psDaily.setLong(4, d.getUid());
			
			// 插入数据，获取生成的自增主键
			psDaily.executeUpdate();
			ResultSet keys = psDaily.getGeneratedKeys();
			keys.next();
			long dailyId = keys.getLong(1);
			
			if (keywords != null && keywords.length > 0) {
				// 查询系统中已有的关键字
				psKeywords = connection.prepareStatement(buildQuery(keywords));
				rsKeywords = psKeywords.executeQuery();
				List<Keyword> existKeywords = getKeywordsResult(rsKeywords);
				
				// 即将和日报关联的关键字id
				List<Long> keywordIds = new ArrayList<>();
				keywordIds.addAll(
					existKeywords.stream().map(k -> k.getId()).collect(Collectors.toList())
				);

				// 先将新的关键字插入数据库
				List<Keyword> newKeywords = getNewKeywords(existKeywords, keywords);
				if (newKeywords.size() > 0) {
					psNewKeywords = connection.prepareStatement(buildNewKeywordsSql(newKeywords), Statement.RETURN_GENERATED_KEYS);
					psNewKeywords.executeUpdate();
					rsNewKeywords = psNewKeywords.getGeneratedKeys();
					
					// 取到新插入的关键字id
					keywordIds.addAll(getNewKeywordIds(rsNewKeywords));
				}
				
				// 更新日报-关键字关联表
				psDailyKeywords = connection.prepareStatement(buildDailyKeywordsSql(dailyId, keywordIds));
				psDailyKeywords.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rsDaily);
			close(psDaily);
			close(rsKeywords);
			close(psKeywords);
			close(rsNewKeywords);
			close(psNewKeywords);
			close(psDailyKeywords);
			close(connection);
		}
	}

	// insert into daily_keyword(did, kid) values (1, 1), (1, 2), (1, 3)
	private static String buildDailyKeywordsSql(long dailyId, List<Long> keywordIds) {
		StringBuilder sb = new StringBuilder("insert into daily_keyword(did, kid) values");
		
		for (Long id : keywordIds) {
			sb.append("(");
			sb.append(dailyId);
			sb.append(",");
			sb.append(id);
			sb.append("),");
		}
		
		sb.deleteCharAt(sb.length() - 1);
		
		return sb.toString();
	}

	// 取到插入的关键字id
	private List<Long> getNewKeywordIds(ResultSet rsNewKeywords) throws SQLException {
		List<Long> ids = new ArrayList<>();
		while (rsNewKeywords.next()) {
			ids.add(rsNewKeywords.getLong(1));
		}
		
		return ids;
	}

	private static String buildNewKeywordsSql(List<Keyword> newKeywords) {
		StringBuilder sb = new StringBuilder();
		sb.append("insert into keyword (name, created_by, updated_by) values ");
		
		for (Keyword k : newKeywords) {
			sb.append("('");
			sb.append(k.getName());
			sb.append("', ");
			sb.append(1);		// created_by
			sb.append(',');		
			sb.append(1);		// updated_by
			sb.append("),");
		}
		
		// 删掉最后一个逗号
		sb.deleteCharAt(sb.length() - 1);

		return sb.toString();
	}

	// 对比已有的关键字记录，和当前要关联的所有关键字对比，看哪些需要插入
	private static List<Keyword> getNewKeywords(List<Keyword> existKeywords, String[] keywords) {
		List<Keyword> newKeywords = Arrays.stream(keywords)
			.map(k -> {
				Keyword keyword = new Keyword();
				keyword.setName(k);
				keyword.setCreated(new Date());
				keyword.setUpdated(new Date());
				
				return keyword;
			})
			// 过滤掉已有的。名字相同即相等，见Keyword对象的equals和hashCode实现
			.filter(k -> !existKeywords.contains(k))
			.collect(Collectors.toList());

		return newKeywords;
	}

	// 读取结果集，将所有关键字记录封装成Keyword对象
	private List<Keyword> getKeywordsResult(ResultSet rsKeywords) throws SQLException {
		List<Keyword> result = new ArrayList<>();
		while (rsKeywords.next()) {
			Keyword k = new Keyword();
			k.setId(rsKeywords.getLong(1));
			k.setName(rsKeywords.getString(2));
			
			result.add(k);
		}
		
		return result;
	}

	// ['key1', 'key2'] ====> "select xxx where name IN ('key1', 'key2')"
	private static String buildQuery(String[] keywords) {
		StringBuilder sb = new StringBuilder();
		
		sb.append("select id, name from keyword where name IN (");
		
		for (String k : keywords) {
			sb.append("'");
			sb.append(k);
			sb.append("',");
		}
		
		// 去掉最后多余的一个逗号
		sb.deleteCharAt(sb.length() - 1);

		sb.append(")");
		
		return sb.toString();
	}
	
	public static void main(String[] args) {
		List<Daily> dailies = new ArrayList<Daily>() {{
			add(new Daily(1L));
			add(new Daily(2L));
			add(new Daily(3L));
		}};
		
		System.out.println(buildDailyKeywordsQuery(dailies));
	}
}
